﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;
using Common.SQL;

namespace MRS.DBL.DAL
{
    public partial class UserDAL
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 判断是否存在
        /// </summary>
        public bool Exists(int Id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select count(1) from users");
            strSQL.Append(" where ");
            strSQL.Append(" Id = @Id  ");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


            int count = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                count = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSQL.ToString(), parameters));
            }
            catch (Exception err)
            {
                logger.Error("users.Exists: " + err);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return count > 0;
        }

        public bool Exists(string userName)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select count(1) from users");
            strSQL.Append(" where ");
            strSQL.Append(" userName = @userName  ");
            MySqlParameter[] parameters = {
					new MySqlParameter("@userName", MySqlDbType.VarChar)
			};
            parameters[0].Value = userName;


            int count = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                count = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSQL.ToString(), parameters));
            }
            catch (Exception err)
            {
                logger.Error("users.Exists UserName: " + err);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return count > 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(MRS.DBL.Model.User model)
        {
            IDbConnection conn = null;
            int effectedrows = 0;

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("insert into users(");
            strSQL.Append("UserName,Password,RealName,Sex,Birthday,RoleId,Status,UnitId,Phone,Email,GroupId,CreateUserId,Online,CreateTime,LastLoginTime,Ext1,Ext2,Ext3");
            strSQL.Append(") values (");
            strSQL.Append("@UserName,@Password,@RealName,@Sex,@Birthday,@RoleId,@Status,@UnitId,@Phone,@Email,@GroupId,@CreateUserId,@Online,@CreateTime,@LastLoginTime,@Ext1,@Ext2,@Ext3");
            strSQL.Append(") ");
            strSQL.Append(";select @@IDENTITY");
            MySqlParameter[] parameters = {
			            new MySqlParameter("@UserName", MySqlDbType.VarChar,15) ,            
                        new MySqlParameter("@Password", MySqlDbType.VarChar,20) ,            
                        new MySqlParameter("@RealName", MySqlDbType.VarChar,25) ,            
                        new MySqlParameter("@Sex", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@Birthday", MySqlDbType.Date) ,            
                        new MySqlParameter("@RoleId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@Status", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@UnitId", MySqlDbType.Int32,11) ,            
                        new MySqlParameter("@Phone", MySqlDbType.VarChar,12) ,            
                        new MySqlParameter("@Email", MySqlDbType.VarChar,25) ,            
                        new MySqlParameter("@GroupId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@CreateUserId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@Online", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@CreateTime", MySqlDbType.DateTime) ,            
                        new MySqlParameter("@LastLoginTime", MySqlDbType.DateTime) ,            
                        new MySqlParameter("@Ext1", MySqlDbType.VarChar,10) ,            
                        new MySqlParameter("@Ext2", MySqlDbType.VarChar,10) ,            
                        new MySqlParameter("@Ext3", MySqlDbType.VarChar,10)             
              
            };

            parameters[0].Value = model.UserName;
            parameters[1].Value = model.Password;
            parameters[2].Value = model.RealName;
            parameters[3].Value = model.Sex;
            parameters[4].Value = model.Birthday;
            parameters[5].Value = model.RoleId;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.UnitId;
            parameters[8].Value = model.Phone;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.GroupId;
            parameters[11].Value = model.CreateUserId;
            parameters[12].Value = model.Online;
            parameters[13].Value = model.CreateTime;
            parameters[14].Value = model.LastLoginTime;
            parameters[15].Value = model.Ext1;
            parameters[16].Value = model.Ext2;
            parameters[17].Value = model.Ext3;
            conn = DBPool.Instance().GetConnection();

            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.Add: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <param name="passWord">加密后的密码</param>
        /// <returns></returns>
        public bool ChangeUserPassWord(int userId, string passWord) 
        {
            string strSQL = "update users set password=@password where id=@userId";
            MySqlParameter[] parameters = 
            {
                new MySqlParameter("@password",MySqlDbType.VarChar),
                new MySqlParameter("@userId",MySqlDbType.Int32)
            };
            parameters[0].Value = passWord;
            parameters[1].Value = userId;

            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL, parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.ChangePassword： " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 修改用户在线状态
        /// </summary>
        /// <param name="userId">用户编号</param>
        /// <param name="isOnline">True 在线  False 离线</param>
        public void ChangeUserOnLineStatus(int userId, bool isOnline) 
        {
            string strSQL = "update users set Online = @Online where id=@userId";
            MySqlParameter[] parameters = 
            {
                new MySqlParameter("@password",MySqlDbType.VarChar),
                new MySqlParameter("@userId",MySqlDbType.Int32)
            };
            parameters[0].Value = isOnline ? MRSUtil.MRSConsts.Code_Yes : MRSUtil.MRSConsts.Code_No;
            parameters[1].Value = userId;

            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL, parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.ChangeUserOnLineStatus： " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(MRS.DBL.Model.User model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update users set ");

            strSQL.Append(" UserName = @UserName , ");
            strSQL.Append(" RealName = @RealName , ");
            strSQL.Append(" Sex = @Sex , ");
            strSQL.Append(" Birthday = @Birthday , ");
            strSQL.Append(" RoleId = @RoleId , ");
            strSQL.Append(" Status = @Status , ");
            strSQL.Append(" UnitId = @UnitId , ");
            strSQL.Append(" Phone = @Phone , ");
            strSQL.Append(" Email = @Email , ");
            strSQL.Append(" GroupId = @GroupId , ");
            strSQL.Append(" CreateUserId = @CreateUserId , ");
            strSQL.Append(" Online = @Online , ");
            strSQL.Append(" CreateTime = @CreateTime , ");
            strSQL.Append(" LastLoginTime = @LastLoginTime , ");
            strSQL.Append(" Ext1 = @Ext1 , ");
            strSQL.Append(" Ext2 = @Ext2 , ");
            strSQL.Append(" Ext3 = @Ext3  ");
            strSQL.Append(" where Id=@Id ");

            MySqlParameter[] parameters = {
			            new MySqlParameter("@Id", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@UserName", MySqlDbType.VarChar,15) ,            
                        new MySqlParameter("@RealName", MySqlDbType.VarChar,25) ,            
                        new MySqlParameter("@Sex", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@Birthday", MySqlDbType.Date) ,            
                        new MySqlParameter("@RoleId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@Status", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@UnitId", MySqlDbType.Int32,11) ,            
                        new MySqlParameter("@Phone", MySqlDbType.VarChar,12) ,            
                        new MySqlParameter("@Email", MySqlDbType.VarChar,25) ,            
                        new MySqlParameter("@GroupId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@CreateUserId", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@Online", MySqlDbType.VarChar,1) ,            
                        new MySqlParameter("@CreateTime", MySqlDbType.DateTime) ,            
                        new MySqlParameter("@LastLoginTime", MySqlDbType.DateTime) ,
                        new MySqlParameter("@Ext1", MySqlDbType.VarChar,10) ,            
                        new MySqlParameter("@Ext2", MySqlDbType.VarChar,10) ,            
                        new MySqlParameter("@Ext3", MySqlDbType.VarChar,10)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.UserName;
            parameters[2].Value = model.RealName;
            parameters[3].Value = model.Sex;
            parameters[4].Value = model.Birthday;
            parameters[5].Value = model.RoleId;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.UnitId;
            parameters[8].Value = model.Phone;
            parameters[9].Value = model.Email;
            parameters[10].Value = model.GroupId;
            parameters[11].Value = model.CreateUserId;
            parameters[12].Value = model.Online;
            parameters[13].Value = model.CreateTime;
            parameters[14].Value = model.LastLoginTime;
            parameters[15].Value = model.Ext1;
            parameters[16].Value = model.Ext2;
            parameters[17].Value = model.Ext3;

            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.Update: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("delete from users ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.Delete: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool BatchDeleteUsers(string Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from users ");
            strSql.Append(" where ID in (" + Idlist + ")  ");
            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSql.ToString(), null);
            }
            catch (Exception ex)
            {
                logger.Error("users.Delete: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }
            return effectedrows > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public MRS.DBL.Model.User GetModel(int Id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select Id, UserName, Password, RealName, Sex, Birthday, RoleId, Status, UnitId, Phone, Email, GroupId, CreateUserId, Online, CreateTime, LastLoginTime, Ext1, Ext2, Ext3  ");
            strSQL.Append("  from users ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


            MRS.DBL.Model.User model = null;

            IDbConnection conn = DBPool.Instance().GetConnection();
            DataSet ds = null;

            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("users.GetModel: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                model = new MRS.DBL.Model.User();

                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserName"] != null && ds.Tables[0].Rows[0]["UserName"].ToString() != "")
                {
                    model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["RealName"] != null && ds.Tables[0].Rows[0]["RealName"].ToString() != "")
                {
                    model.RealName = ds.Tables[0].Rows[0]["RealName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Sex"] != null && ds.Tables[0].Rows[0]["Sex"].ToString() != "")
                {
                    model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Birthday"] != null && ds.Tables[0].Rows[0]["Birthday"].ToString() != "")
                {
                    model.Birthday = DateTime.Parse(ds.Tables[0].Rows[0]["Birthday"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                {
                    model.RoleId = int.Parse(ds.Tables[0].Rows[0]["RoleId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UnitId"] != null && ds.Tables[0].Rows[0]["UnitId"].ToString() != "")
                {
                    model.UnitId = int.Parse(ds.Tables[0].Rows[0]["UnitId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["GroupId"] != null && ds.Tables[0].Rows[0]["GroupId"].ToString() != "")
                {
                    model.GroupId = int.Parse(ds.Tables[0].Rows[0]["GroupId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreateUserId"] != null && ds.Tables[0].Rows[0]["CreateUserId"].ToString() != "")
                {
                    model.CreateUserId = int.Parse(ds.Tables[0].Rows[0]["CreateUserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Online"] != null && ds.Tables[0].Rows[0]["Online"].ToString() != "")
                {
                    model.Online = ds.Tables[0].Rows[0]["Online"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Ext1"] != null && ds.Tables[0].Rows[0]["Ext1"].ToString() != "")
                {
                    model.Ext1 = ds.Tables[0].Rows[0]["Ext1"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Ext2"] != null && ds.Tables[0].Rows[0]["Ext2"].ToString() != "")
                {
                    model.Ext2 = ds.Tables[0].Rows[0]["Ext2"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Ext3"] != null && ds.Tables[0].Rows[0]["Ext3"].ToString() != "")
                {
                    model.Ext3 = ds.Tables[0].Rows[0]["Ext3"].ToString();
                }
            }

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public MRS.DBL.Model.User[] GetList()
        {
            IDbConnection conn = null;
            string strSQL = null;
            DataSet ds = null;
            List<MRS.DBL.Model.User> lstModel = null;

            strSQL = "select * FROM users";

            conn = DBPool.Instance().GetConnection();
            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), null);
            }
            catch (Exception e)
            {
                logger.Error("users.GetList:" + e);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            lstModel = new List<MRS.DBL.Model.User>();

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                MRS.DBL.Model.User model = new MRS.DBL.Model.User();

                if (ds.Tables[0].Rows[i]["Id"] != null && ds.Tables[0].Rows[i]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[i]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[i]["UserName"] != null && ds.Tables[0].Rows[i]["UserName"].ToString() != "")
                {
                    model.UserName = ds.Tables[0].Rows[i]["UserName"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Password"] != null && ds.Tables[0].Rows[i]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[i]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[i]["RealName"] != null && ds.Tables[0].Rows[i]["RealName"].ToString() != "")
                {
                    model.RealName = ds.Tables[0].Rows[i]["RealName"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Sex"] != null && ds.Tables[0].Rows[i]["Sex"].ToString() != "")
                {
                    model.Sex = ds.Tables[0].Rows[i]["Sex"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Birthday"] != null && ds.Tables[0].Rows[i]["Birthday"].ToString() != "")
                {
                    model.Birthday = DateTime.Parse(ds.Tables[0].Rows[i]["Birthday"].ToString());
                }
                if (ds.Tables[0].Rows[i]["RoleId"] != null && ds.Tables[0].Rows[i]["RoleId"].ToString() != "")
                {
                    model.RoleId = int.Parse(ds.Tables[0].Rows[i]["RoleId"].ToString());
                }
                if (ds.Tables[0].Rows[i]["Status"] != null && ds.Tables[0].Rows[i]["Status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[i]["Status"].ToString();
                }
                if (ds.Tables[0].Rows[i]["UnitId"] != null && ds.Tables[0].Rows[i]["UnitId"].ToString() != "")
                {
                    model.UnitId = int.Parse(ds.Tables[0].Rows[i]["UnitId"].ToString());
                }
                if (ds.Tables[0].Rows[i]["Phone"] != null && ds.Tables[0].Rows[i]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[i]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Email"] != null && ds.Tables[0].Rows[i]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[i]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[i]["GroupId"] != null && ds.Tables[0].Rows[i]["GroupId"].ToString() != "")
                {
                    model.GroupId = int.Parse(ds.Tables[0].Rows[i]["GroupId"].ToString());
                }
                if (ds.Tables[0].Rows[i]["CreateUserId"] != null && ds.Tables[0].Rows[i]["CreateUserId"].ToString() != "")
                {
                    model.CreateUserId = int.Parse(ds.Tables[0].Rows[i]["CreateUserId"].ToString());
                }
                if (ds.Tables[0].Rows[i]["Online"] != null && ds.Tables[0].Rows[i]["Online"].ToString() != "")
                {
                    model.Online = ds.Tables[0].Rows[i]["Online"].ToString();
                }
                if (ds.Tables[0].Rows[i]["CreateTime"] != null && ds.Tables[0].Rows[i]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[i]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[i]["LastLoginTime"] != null && ds.Tables[0].Rows[i]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(ds.Tables[0].Rows[i]["LastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[i]["Ext1"] != null && ds.Tables[0].Rows[i]["Ext1"].ToString() != "")
                {
                    model.Ext1 = ds.Tables[0].Rows[i]["Ext1"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Ext2"] != null && ds.Tables[0].Rows[i]["Ext2"].ToString() != "")
                {
                    model.Ext2 = ds.Tables[0].Rows[i]["Ext2"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Ext3"] != null && ds.Tables[0].Rows[i]["Ext3"].ToString() != "")
                {
                    model.Ext3 = ds.Tables[0].Rows[i]["Ext3"].ToString();
                }

                lstModel.Add(model);
            }

            return lstModel.ToArray();
        }
    }
}